# -*- coding: utf-8 -*-
from odoo import fields, models, tools, api

ARRIVE_TIMESPAN = [(1, '0-5分钟'),
                   (2, '5-10分钟'),
                   (3, '10-15分钟'),
                   (4, '15-20分钟'),
                   (5, '20-25分钟'),
                   (6, '25-30分钟'),
                   (7, '30分钟以上')]

TRAP_TIMESPAN = [(0, '0点-2点'),
                 (1, '2点-4点'),
                 (2, '4点-6点'),
                 (3, '6点-8点'),
                 (4, '8点-10点'),
                 (5, '10点-12点'),
                 (6, '12点-14点'),
                 (7, '14点-16点'),
                 (8, '16点-18点'),
                 (9, '18点-20点'),
                 (10, '20点-22点'),
                 (11, '22点-24点'),]

LIFT_AGE_TIMESPAN = [
    ('under_five_years', '5年以内'),
    ('five_to_ten_years', '5-10年'),
    ('ten_to_fifteen_years', '10-15年'),
    ('above_fifteen_years', '15年以内')
]


class ResponseTimespan(models.Model):
    # 响应时间统计

    _name = "tyibs.lift.er.response.timespan.report"
    _auto = False

    response_timespan = fields.Selection(ARRIVE_TIMESPAN, string=u"平均救援到达时间", readonly=True)
    save_count = fields.Integer(string=u"救援次数", readonly=True)
    save_percent = fields.Float(string=u"占百分比(%)", readonly=True)
    service_time = fields.Datetime(string=u"日期")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_response_timespan_report")
        self._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_response_timespan_report AS (
            select
                  min(transform_result.id) as id,
				  transform_result.service_time,
                  (select cast (CASE WHEN transform_result.timespan > 6 THEN 7 ELSE transform_result.timespan END as INTEGER )) as response_timespan,
                  count(transform_result.id) as save_count,
                  (SELECT round(count(transform_result.id)*100 ::numeric / (SELECT count(id) FROM tyibs_er_work_order_save) ::numeric, 2)) as save_percent
                  from (select
                          save_orders.id,
						save_orders.service_time,
                          (select floor((select
                            floor(extract(epoch from (
                              select (response_time - service_time)/60
                                from tyibs_er_work_order_save as t
                                where t.id = save_orders.id
                    )))
                    )/5) + 1)  as timespan
                from tyibs_er_work_order_save as save_orders) as transform_result group by transform_result.timespan, transform_result.service_time
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(ResponseTimespan, self).read_group(domain, fields, groupby, offset, limit, "save_count desc", lazy)


class ArriveTimespan(models.Model):
    # 救援到达时间统计

    _name = "tyibs.lift.er.arrive.timespan.report"
    _auto = False

    arrive_timespan = fields.Selection(ARRIVE_TIMESPAN, string=u"平均救援到达时间")
    save_count = fields.Integer(string=u"救援次数")
    save_percent = fields.Float(string=u"占百分比(%)", readonly=True)
    service_time = fields.Datetime(string=u"日期")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_arrive_timespan_report")
        self._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_arrive_timespan_report AS (
                select
                  min(transform_result.id) as id,
                  transform_result.service_time,
                  (select cast (CASE WHEN transform_result.timespan > 6 THEN 7 ELSE transform_result.timespan END as INTEGER )) as arrive_timespan,
                  count(transform_result.id) as save_count,
                  (SELECT round(count(transform_result.id)*100 ::numeric / (SELECT count(id) FROM tyibs_er_work_order_save) ::numeric, 2)) as save_percent
                  from (select
                          save_orders.id,
                          save_orders.service_time,
                          (select floor((select
                            floor(extract(epoch from (
                              select (do_arrived_time - service_time)/60
                                from tyibs_er_work_order_save as t
                                where t.id = save_orders.id
                    )))
                    )/5) + 1)  as timespan
                from tyibs_er_work_order_save as save_orders) as transform_result group by transform_result.timespan, transform_result.service_time
            )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(ArriveTimespan, self).read_group(domain, fields, groupby, offset, limit, "save_count desc", lazy)


class SaveFinishedTimespan(models.Model):
    # 救援完成时间统计

    _name = "tyibs.lift.er.save.finished.timespan.report"
    _auto = False

    save_finished_timespan = fields.Selection(ARRIVE_TIMESPAN, string=u"救援完成时间", readonly=True)
    save_count = fields.Integer(string=u"救援次数", readonly=True)
    save_percent = fields.Float(string=u"占百分比(%)", readonly=True)
    service_time = fields.Datetime(string=u"日期")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_save_finished_timespan_report")
        self._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_save_finished_timespan_report AS (
            select
                  min(transform_result.id) as id,
				  transform_result.service_time,
                  (select cast (CASE WHEN transform_result.timespan > 6 THEN 7 ELSE transform_result.timespan END as INTEGER )) as save_finished_timespan ,
                  count(transform_result.id) as save_count,
                  (SELECT round(count(transform_result.id)*100 ::numeric / (SELECT count(id) FROM tyibs_er_work_order_save) ::numeric, 2)) as save_percent
                  from (select
                          save_orders.id,
						save_orders.service_time,
                          (select floor((select
                            floor(extract(epoch from (
                              select (do_finish_time - service_time)/60
                                from tyibs_er_work_order_save as t
                                where t.id = save_orders.id
                    )))
                    )/5) + 1)  as timespan
                from tyibs_er_work_order_save as save_orders) as transform_result group by transform_result.timespan, transform_result.service_time
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(SaveFinishedTimespan, self).read_group(domain, fields, groupby, offset, limit, "save_count desc", lazy)


class TrapTimespan(models.Model):
    # 困人时间段统计

    _name = "tyibs.lift.er.trap.timespan.report"
    _auto = False

    # trap_timespan = fields.Char(string=u"困人时间")
    trap_timespan = fields.Selection(TRAP_TIMESPAN, string=u"困人时间段")
    save_count = fields.Integer(string=u"救援次数", readonly=True)
    save_percent = fields.Float(string=u"占百分比(%)", readonly=True)
    service_time = fields.Datetime(string=u"日期")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_trap_timespan_report")
        self._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_trap_timespan_report AS (
                select
                    min(id) as id,
                    count(id) as save_count,
                    round(count(id) * 100 ::numeric /(select count(id) from tyibs_er_work_order_save) ::numeric, 2) as save_percent,
                    (select (extract(hour from service_time at time zone 'PST'))) ::int /2 as trap_timespan,
                    service_time at time zone 'PST' as service_time
                from tyibs_er_work_order_save
                group by trap_timespan, service_time
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(TrapTimespan, self).read_group(domain, fields, groupby, offset, limit, "save_count desc", lazy)


class UseCompanyReport(models.Model):
    # 使用单位发生救援情况/困人统计

    _name = "tyibs.lift.er.use.company.report"
    _auto = False

    use_company_id = fields.Many2one("tyibs.base.lift.use.company", string=u"使用单位")
    trap_count = fields.Integer(string=u"困人次数")
    lift_count = fields.Integer(string=u"电梯数")
    trap_percent = fields.Float(string=u"困人率(%)")
    all_percent = fields.Float(string=u"占百分比(%)")
    save_time = fields.Datetime(string=u"时间")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_use_company_report")
        self._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_use_company_report AS (
                select
                    transform_result.id as id,
                    transform_result.use_company_id,
                    transform_result.trap_count,
                    transform_result.lift_count,
                    transform_result.save_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.lift_count :: numeric, 2) as trap_percent,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_save_count :: numeric, 2) as all_percent
                from
                    (select
                        min(save.id) as id,
                        save.lift_use_company_id as use_company_id,
                        count(save.id) as trap_count,
                        (select count(id) from tyibs_er_work_order_save) as all_save_count,
                        save.service_time as save_time,
                        use_company.lift_count
                    from tyibs_er_work_order_save as save
                    left join tyibs_base_lift_use_company as use_company
                    on save.lift_use_company_id = use_company.id
                    group by save.lift_use_company_id, use_company.lift_count, save.service_time
                    order by save.lift_use_company_id) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(UseCompanyReport, self).read_group(domain, fields, groupby, offset, limit, "trap_count desc", lazy)


class MaintenanceCompanySaveReport(models.Model):
    # 维保单位救援统计

    _name = "tyibs.lift.er.maintenance.company.report"
    _auto = False

    main_company_id = fields.Many2one("tyibs.base.maintenance.company", string=u"维保单位")
    # trap_count = fields.Integer(string=u"困人次数")
    # save_count = fields.Integer(string=u"救援次数")
    save_level_one_count = fields.Integer(string=u"一级救援次数")
    save_level_two_count = fields.Integer(string=u"二级救援次数")
    save_count = fields.Integer(string=u"总救援次数")
    # lift_count = fields.Integer(string=u"维保电梯数")
    arrive_time_sum = fields.Float(string=u"到达现场总耗时(分钟)")
    arrive_time_avg = fields.Float(string=u"到达现场平均用时(分钟)")
    # trap_percent = fields.Float(string=u"困人率(%)")
    save_percent = fields.Float(string=u"救援率(%)")
    save_time = fields.Datetime(string=u"时间")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_maintenance_company_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_maintenance_company_report AS (
                select
                    left_table.id,
                    left_table.main_company_id,
                    right_table.save_level_one_count,
                    right_table.service_time as save_time,
                    left_table.sum_save_count,
                    right_table.save_level_two_count,
                    (right_table.save_level_one_count + right_table.save_level_two_count) as save_count,
                    round((right_table.save_level_one_count + right_table.save_level_two_count) * 100 :: numeric / left_table.sum_save_count :: numeric, 2) as save_percent,
                    right_table.arrive_time_sum,
                    right_table.arrive_time_avg
                from
                    (select
                        min(save.id) as id,
                        save.lift_maintenance_company_id as main_company_id,
                        count(save.id) as trap_count,
                        (select count(aas.id) from tyibs_er_work_order_save as aas where aas.save_company_id is not null) as sum_save_count
                    from tyibs_er_work_order_save as save
                    group by save.lift_maintenance_company_id) as left_table
                    left join
                    (select
                        t_save.save_company_id as main_company_id,
						t_save.service_time,
                        count(t_save.id) filter(where t_save.save_company_id = main.id and t_save.lift_maintenance_company_id != main.id) as save_level_two_count,
						count(t_save.id) filter(where t_save.save_company_id = t_save.lift_maintenance_company_id) as save_level_one_count,
                        round(extract(epoch from(sum(t_save.do_arrived_time - t_save.service_time))) :: numeric / 60, 1) as arrive_time_sum,
                        round(extract(epoch from(sum(t_save.do_arrived_time - t_save.service_time))) :: numeric / count(t_save.id) / 60, 1) as arrive_time_avg
                    from tyibs_er_work_order_save as t_save
                    left join tyibs_base_maintenance_company as main
                    on t_save.save_company_id = main.id
                    group by t_save.save_company_id, t_save.service_time) as right_table
                    on left_table.main_company_id = right_table.main_company_id
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(MaintenanceCompanySaveReport, self).read_group(domain, fields, groupby, offset, limit, "save_percent desc", lazy)


class MaintenanceCompanyTrapReport(models.Model):
    # 维保单位发生困人统计

    _name = "tyibs.lift.er.maintenance.company.trap.report"
    _auto = False

    main_company_id = fields.Many2one("tyibs.base.maintenance.company", string=u"维保单位")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_maintenance_company_trap_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_maintenance_company_trap_report AS (
                select
                    transform_result.id,
                    transform_result.main_company_id,
                    transform_result.trap_count,
                    transform_result.service_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_lift_count, 2) as trap_percent
                from
                    (select
                        min(save.id) as id,
                        save.lift_maintenance_company_id as main_company_id,
                        count(save.id) as trap_count,
						(select count(id) from tyibs_er_work_order_save) as all_lift_count,
                        save.service_time,
                        (select count(id) from tyibs_base_lift where maintenance_company_id = save.lift_maintenance_company_id) as lift_count
                    from tyibs_er_work_order_save as save
                    group by save.lift_maintenance_company_id, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(MaintenanceCompanyTrapReport, self).read_group(domain, fields, groupby, offset, limit,
                                                                    "trap_count desc", lazy)


class LiftFactoryTrapReport(models.Model):
    # 电梯制造单位(品牌)发生困人统计

    _name = "tyibs.lift.er.lift.factory.trap.report"
    _auto = False

    factory_id = fields.Many2one("tyibs.base.lift.factory", string=u"制造单位")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_lift_factory_trap_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_lift_factory_trap_report AS (
                 select
                    transform_result.id,
                    transform_result.factory_id,
                    transform_result.trap_count,
					transform_result.service_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_trap_count, 2) as trap_percent
                 from
                 (select
                    min(save.id) as id,
					save.service_time,
                    factory.id as factory_id,
                    factory.name,
                    count(save.id) as trap_count,
                    (select count(id) from tyibs_er_work_order_save) as all_trap_count
                 from tyibs_er_work_order_save as save
                 left join tyibs_base_lift as lift
                 on save.lift_id = lift.id
                 left join tyibs_base_lift_factory as factory
                 on lift.factory_id = factory.id
                 group by factory.id, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(LiftFactoryTrapReport, self).read_group(domain, fields, groupby, offset, limit,
                                                                    "trap_count desc", lazy)


class AreaTrapReport(models.Model):
    # 电梯所在区域发生困人统计

    _name = "tyibs.lift.er.area.trap.report"
    _auto = False

    area_id = fields.Many2one("tyibs.base.province.city.area", string=u"区/县")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")


    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_area_trap_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_area_trap_report AS (
                 select
                    transform_result.id,
                    transform_result.area_id,
                    transform_result.trap_count,
					transform_result.service_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_trap_count, 2) as trap_percent
                 from
                 (select
                    min(save.id) as id,
					save.service_time,
                    area.id as area_id,
                    count(save.id) as trap_count,
                    (select count(id) from tyibs_er_work_order_save) as all_trap_count
                 from tyibs_er_work_order_save as save
                 left join tyibs_base_lift as lift
                 on save.lift_id = lift.id
                 left join tyibs_base_province_city_area as area
                 on lift.area_id = area.id
                 group by area.id, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(AreaTrapReport, self).read_group(domain, fields, groupby, offset, limit,
                                                                    "trap_count desc", lazy)


class FaultTrapReport(models.Model):
    # 电梯困人故障原因统计

    _name = "tyibs.lift.er.fault.trap.report"
    _auto = False

    fault_item_id = fields.Many2one("tyibs.base.fault.item", string=u"故障原因")
    fault_category_id = fields.Many2one("tyibs.base.fault.category", related="fault_item_id.category_id", store=True, string=u"故障类别")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_fault_trap_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_fault_trap_report AS (
                 select
                    transform_result.id,
                    transform_result.fault_category_id,
					transform_result.fault_item_id,
                    transform_result.trap_count,
					transform_result.service_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_trap_count, 2) as trap_percent
                 from
                 (select
                    min(save.id) as id,
					save.service_time,
                    fault_category.id as fault_category_id,
                    count(save.id) as trap_count,
					save.fault_item_id,
                    (select count(id) from tyibs_er_work_order_save) as all_trap_count
                 from tyibs_er_work_order_save as save
                 left join tyibs_base_fault_category as fault_category
                 on save.fault_category_id = fault_category.id
				 left join tyibs_base_fault_item as fault_item
				 on save.fault_item_id = fault_item.id
                 group by fault_category.id,save.fault_item_id, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(FaultTrapReport, self).read_group(domain, fields, groupby, offset, limit,
                                                                    "trap_count desc", lazy)


class LiftAgeTrapReport(models.Model):
    # 电梯使用年限统计

    _name = "tyibs.lift.er.lift.age.trap.report"
    _auto = False

    lift_age_timespan = fields.Selection(LIFT_AGE_TIMESPAN, string=u"使用年数")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_lift_age_trap_report")
        self.sudo()._cr.execute("""
            CREATE or REPLACE VIEW tyibs_lift_er_lift_age_trap_report AS (
                 select
                    transform_result.id,
                    transform_result.trap_count,
					transform_result.service_time,
					transform_result.lift_age_timespan,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_trap_count, 2) as trap_percent
                 from
                 (select
                    min(save.id) as id,
					save.service_time,
                    count(save.id) as trap_count,
					lift.lift_age as lift_age_timespan,
                    (select count(id) from tyibs_er_work_order_save) as all_trap_count
                 from tyibs_er_work_order_save as save
                 left join tyibs_base_lift as lift
                 on save.lift_id = lift.id
                 group by lift_age_timespan, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(LiftAgeTrapReport, self).read_group(domain, fields, groupby, offset, limit,
                                                       "trap_count desc", lazy)


class TrapByPlaceReport(models.Model):
    # 电梯使用场所困人统计

    _name = "tyibs.lift.er.trap.place.report"
    _auto = False

    place_id = fields.Many2one("tyibs.base.lift.place", string=u"使用场所")
    trap_count = fields.Integer(string=u"困人次数")
    trap_percent = fields.Float(string=u"困人率(%)")
    service_time = fields.Datetime(string=u"时间")

    def init(self):
        tools.drop_view_if_exists(self._cr, "tyibs_lift_er_trap_place_report")
        self.sudo()._cr.execute("""
              CREATE or REPLACE VIEW tyibs_lift_er_trap_place_report AS (
                 select
                    transform_result.id,
					transform_result.place_id,
                    transform_result.trap_count,
					transform_result.service_time,
                    round(transform_result.trap_count * 100 :: numeric / transform_result.all_trap_count, 2) as trap_percent
                 from
                 (select
                    min(save.id) as id,
					save.service_time,
                    place.id as place_id,
                    count(save.id) as trap_count,
                    (select count(id) from tyibs_er_work_order_save) as all_trap_count
                 from tyibs_er_work_order_save as save
                 left join tyibs_base_lift as lift
                 on save.lift_id = lift.id
                 left join tyibs_base_lift_place as place
				 on lift.place_id = place.id
                 group by place.id, save.service_time) as transform_result
        )""")

    @api.model
    def read_group(self, domain, fields, groupby, offset=0, limit=None, orderby=False, lazy=True):
        return super(TrapByPlaceReport, self).read_group(domain, fields, groupby, offset, limit,
                                                                    "trap_count desc", lazy)
